package tx.bjdacp.service;

import static junit.framework.Assert.assertNotNull;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;

import tx.bjdacp.model.Zone;

import cn.org.rapid_framework.test.context.TestMethodContextExecutionListener;

@ContextConfiguration(locations = { "classpath:/spring/*-resource.xml",
		"classpath:/spring/*-validator.xml",
		"classpath:/spring/*-datasource.xml", "classpath:/spring/*-dao.xml",
		"classpath:/spring/*-service.xml" })
@TestExecutionListeners(listeners = TestMethodContextExecutionListener.class)
public class InitDataTest extends AbstractTransactionalJUnit4SpringContextTests {
	protected Log log = LogFactory.getLog(getClass());

	protected DataSource getDataSource() {
		DataSource ds = (DataSource) applicationContext.getBean("dataSource");
		assertNotNull("not found 'dataSource'", ds);
		return ds;
	}

	@Test
	@Rollback(false)
	public void init_bjdacp_drug_sell_unit__date() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("SELECT * FROM 药品经营企业目录");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				init_unit_found_date(conn, rs, id);
				init_logout_date(conn, rs, id);

//				init_licence_give_date(conn, rs, id,"许可证发证日期");
//				init_licence_expiry_date(conn, rs, id,"许可证有效日期");
//				init_gsp_give_date(conn, rs, id,"认证证书发证日期");
//				init_gsp_expiry_date(conn, rs, id,"认证证书有效期");

				init_licence_give_date(conn, rs, id,"第二次许可证发证日期");
				init_licence_expiry_date(conn, rs, id,"第二次许可证有效日期");
				init_gsp_give_date(conn, rs, id,"第二次认证证书发证日期");
				init_gsp_expiry_date(conn, rs, id,"第二次认证证书有效期");

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	@Test
	@Rollback(false)
	public void init_bjdacp_drug_sell_unit__unit_name() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("SELECT * FROM 药品经营企业目录");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String unitName=rs.getString("企业名称");
				String oldUnitName=rs.getString("原企业名称");

				if(oldUnitName==null) continue;

				PreparedStatement ps2 = conn
				.prepareStatement("SELECT * FROM bjdacp_drug_sell_unit where unit_name='"+unitName+"'");
				ResultSet rs2 = ps2.executeQuery();
				if(rs2.next()){
					int did=rs2.getInt("drug_sell_unit_id");
					conn.prepareStatement(	("Insert Into bjdacp_drug_sell_unit_modify_log (drug_sell_unit_id,user_info_id,unit_name,modify_date,modify_proceeding) values ("+did+",1,'"+oldUnitName+"',getdate(),'变更企业名称')")).executeUpdate();
				}
			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	@Test
	@Rollback(false)
	public void init_bjdacp_drug_sell_unit__resend_lic_log__license() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("SELECT * FROM 药品经营企业目录");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String unitName=rs.getString("企业名称");
				String oldUnitName=rs.getString("原企业名称");

				String newLicCode=rs.getString("新许可证号");
				String newLicGiveDate=rs.getString("第二次许可证发证日期");
				String newExpiryDate=rs.getString("第二次许可证有效日期");

				if(newLicCode==null) continue;

				String oldLicCode=rs.getString("原许可证号");
				String oldLicGiveDate=rs.getString("许可证发证日期");
				String oldExpiryDate=rs.getString("许可证有效日期");

				PreparedStatement ps2 = conn
				.prepareStatement("SELECT * FROM bjdacp_drug_sell_unit where unit_name='"+unitName+"'");
				ResultSet rs2 = ps2.executeQuery();
				if(rs2.next()){
					int did=rs2.getInt("drug_sell_unit_id");
					//conn.prepareStatement(	("Insert Into bjdacp_resend_lic_log (drug_sell_unit_id,lic_code,lic_give_date,lic_expiry_date,lic_type) values ("+did+",'"+oldLicCode+"','"+oldLicGiveDate+"','"+oldExpiryDate+"','许可证')")).executeUpdate();
					conn.prepareStatement("update bjdacp_drug_sell_unit set licence_code='"+newLicCode+"' , licence_give_date='"+newLicGiveDate+"' , licence_expiry_date='"+newExpiryDate+"' , is_resend_license=1 where drug_sell_unit_id="+did).executeUpdate();
				}
			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	@Test
	@Rollback(false)
	public void init_bjdacp_drug_sell_unit__resend_lic_log__gsp() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("SELECT * FROM 药品经营企业目录");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String unitName=rs.getString("企业名称");
				String oldUnitName=rs.getString("原企业名称");

				String newLicCode=rs.getString("第二次认证证书号");
				String newLicGiveDate=rs.getString("第二次认证证书发证日期");
				String newExpiryDate=rs.getString("第二次认证证书有效期");

				if(newLicCode==null) continue;

				String oldLicCode=rs.getString("认证证书号");
				String oldLicGiveDate=rs.getString("认证证书发证日期");
				String oldExpiryDate=rs.getString("认证证书有效期");

				PreparedStatement ps2 = conn
				.prepareStatement("SELECT * FROM bjdacp_drug_sell_unit where unit_name='"+unitName+"'");
				ResultSet rs2 = ps2.executeQuery();
				if(rs2.next()){
					int did=rs2.getInt("drug_sell_unit_id");
//					conn.prepareStatement(	("Insert Into bjdacp_resend_lic_log (drug_sell_unit_id,lic_code,lic_give_date,lic_expiry_date,lic_type) values ("+did+",'"+oldLicCode+"','"+oldLicGiveDate+"','"+oldExpiryDate+"','GSP认证')")).executeUpdate();
					conn.prepareStatement("update bjdacp_drug_sell_unit set gsp_code='"+newLicCode+"' , gsp_give_date='"+newLicGiveDate+"' , gsp_expiry_date='"+newExpiryDate+"'  where drug_sell_unit_id="+did).executeUpdate();
				}
			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	private ZoneManager zoneManager;
	@Autowired
	public void setZoneManager(ZoneManager manager) {
		this.zoneManager = manager;
	}

	@Test
	@Rollback(false)
	public void init_bjdacp_drug_sell_unit__zone() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("SELECT * FROM bjdacp_drug_sell_unit");
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				int zone_id=0;
				int id = rs.getInt("drug_sell_unit_id");
				String regist_address=rs.getString("regist_address");
				List<Zone> lz=zoneManager.findAll();
				for (Zone zone : lz) {
					if(regist_address.contains(zone.getZoneName())){
						zone_id=zone.getZoneId();
					}
				}
				if(zone_id==0) zone_id=4;//城区
				if(zone_id!=0){
					conn.prepareStatement(
							("update   bjdacp_drug_sell_unit set zone_id=" + zone_id
									+ " where drug_sell_unit_id=" + id)).executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	@Test
	@Rollback(false)
	public void init_bjdacp_wang_dian__zone() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("SELECT * FROM bjdacp_wang_dian");
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				int zone_id=0;
				int id = rs.getInt("wang_dian_id");
				String address=rs.getString("address");
				List<Zone> lz=zoneManager.findAll();
				for (Zone zone : lz) {
					if(address.contains(zone.getZoneName())){
						zone_id=zone.getZoneId();
					}
				}
				if(zone_id==0) zone_id=4;//城区
				if(zone_id!=0){
					conn.prepareStatement(
							("update   bjdacp_wang_dian set zone_id=" + zone_id
									+ " where wang_dian_id=" + id)).executeUpdate();
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Test
	@Rollback(false)
	public void init_bjdacp_drug_sell_unit__is_sell_chinese_medicine() {
		try {
			Connection conn = getDataSource().getConnection();
			PreparedStatement ps = conn
					.prepareStatement("SELECT * FROM 中药饮片");
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {

				String unitName=rs.getString("企业名称");

					int i=conn.prepareStatement(
							("update   bjdacp_drug_sell_unit set is_sell_chinese_medicine=1" + " where unit_name='" + unitName+"'")).executeUpdate();
				if(i!=1){
					log.info(unitName);
				}

			}

			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	private void init_unit_found_date(Connection conn, ResultSet rs, int id)
			throws SQLException {
		String unit_found_date = rs.getString("企业成立日期");
		if (unit_found_date == null || unit_found_date.length() == 10){
			return;
		}else{
			log.info(id);
		}

		if (unit_found_date.length() == 4) {
			unit_found_date += "0101";
		}
		if (unit_found_date.length() == 6) {
			unit_found_date += "01";
		}
		if (unit_found_date.length() == 8) {
			char[] c = unit_found_date.toCharArray();
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < c.length; i++) {
				sb.append(c[i]);
				if (i == 3 || i == 5) {
					sb.append("-");
				}
			}
			unit_found_date = sb.toString();
		}

		conn.prepareStatement(
				("update 药品经营企业目录 set 企业成立日期='" + unit_found_date
						+ "' where id=" + id)).executeUpdate();

	}

	private void init_licence_give_date(Connection conn, ResultSet rs, int id,String field)
			throws SQLException {
		String licence_give_date = rs.getString(field);
		if (licence_give_date == null || licence_give_date.length() == 10){
			return;
		}else{
			log.info(id);
		}

		if (licence_give_date.length() == 4) {
			licence_give_date += "0101";
		}
		if (licence_give_date.length() == 6) {
			licence_give_date += "01";
		}
		if (licence_give_date.length() == 8) {
			char[] c = licence_give_date.toCharArray();
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < c.length; i++) {
				sb.append(c[i]);
				if (i == 3 || i == 5) {
					sb.append("-");
				}
			}
			licence_give_date = sb.toString();
		}

		conn.prepareStatement(
				("update 药品经营企业目录 set "+field+"='" + licence_give_date
						+ "' where id=" + id)).executeUpdate();

	}

	private void init_licence_expiry_date(Connection conn, ResultSet rs, int id,String field)
			throws SQLException {
		String licence_expiry_date = rs.getString(field);
		if (licence_expiry_date == null || licence_expiry_date.length() == 10){
			return;
		}else{
			log.info(id);
		}

		if (licence_expiry_date.length() == 4) {
			licence_expiry_date += "0101";
		}
		if (licence_expiry_date.length() == 6) {
			licence_expiry_date += "01";
		}
		if (licence_expiry_date.length() == 8) {
			char[] c = licence_expiry_date.toCharArray();
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < c.length; i++) {
				sb.append(c[i]);
				if (i == 3 || i == 5) {
					sb.append("-");
				}
			}
			licence_expiry_date = sb.toString();
		}

		conn.prepareStatement(
				("update 药品经营企业目录 set "+field+"='" + licence_expiry_date
						+ "' where id=" + id)).executeUpdate();

	}

	private void init_gsp_give_date(Connection conn, ResultSet rs, int id,String field)
			throws SQLException {
		String gsp_give_date = rs.getString(field);
		if (gsp_give_date == null || gsp_give_date.length() == 10){
			return;
		}else{
			log.info(id);
		}

		if (gsp_give_date.length() == 4) {
			gsp_give_date += "0101";
		}
		if (gsp_give_date.length() == 6) {
			gsp_give_date += "01";
		}
		if (gsp_give_date.length() == 8) {
			char[] c = gsp_give_date.toCharArray();
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < c.length; i++) {
				sb.append(c[i]);
				if (i == 3 || i == 5) {
					sb.append("-");
				}
			}
			gsp_give_date = sb.toString();
		}

		conn.prepareStatement(
				("update 药品经营企业目录 set "+field+"='" + gsp_give_date
						+ "' where id=" + id)).executeUpdate();

	}

	private void init_gsp_expiry_date(Connection conn, ResultSet rs, int id,String field)
			throws SQLException {
		String gsp_expiry_date = rs.getString(field);
		if (gsp_expiry_date == null || gsp_expiry_date.length() == 10){
			return;
		}else{
			log.info(id);
		}

		if (gsp_expiry_date.length() == 4) {
			gsp_expiry_date += "0101";
		}
		if (gsp_expiry_date.length() == 6) {
			gsp_expiry_date += "01";
		}
		if (gsp_expiry_date.length() == 8) {
			char[] c = gsp_expiry_date.toCharArray();
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < c.length; i++) {
				sb.append(c[i]);
				if (i == 3 || i == 5) {
					sb.append("-");
				}
			}
			gsp_expiry_date = sb.toString();
		}

		conn.prepareStatement(
				("update 药品经营企业目录 set "+field+"='" + gsp_expiry_date
						+ "' where id=" + id)).executeUpdate();

	}

	private void init_logout_date(Connection conn, ResultSet rs, int id)
			throws SQLException {
		String logout_date = rs.getString("注销日期");
		if (logout_date == null || logout_date.length() == 10){
			return;
		}else{
			log.info(id);
		}


		if (logout_date.length() == 4) {
			logout_date += "0101";
		}
		if (logout_date.length() == 6) {
			logout_date += "01";
		}
		if (logout_date.length() == 8) {
			char[] c = logout_date.toCharArray();
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < c.length; i++) {
				sb.append(c[i]);
				if (i == 3 || i == 5) {
					sb.append("-");
				}
			}
			logout_date = sb.toString();
		}

		conn.prepareStatement(
				("update 药品经营企业目录 set 注销日期='" + logout_date + "' where id=" + id))
				.executeUpdate();

	}

}
